﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_Project_GetList_CreateUser]
AS
Begin
	Create Table #tmpTable (CompanyID int, CreateUser varchar(30))
	Declare myCur CURSOR FOR Select id From Company Order By id
	
	Declare @nID int
	Set @nID = 0

	Open myCur
	FETCH NEXT FROM myCur INTO @nID
	While @@fetch_status = 0
	Begin
		;WITH List1 As(
		Select Top 200 CreateUser
		From Project
		Where companyid = @nID
		Order By id Desc
		)
		,
		List2 As(
			Select List1.* From List1,Employee Where List1.CreateUser=Employee.UserName And Employee.State In(3,4)
		)

		Insert Into #tmpTable Select @nID,CreateUser From List2 Where Left(CreateUser,4)>'2007' Group By CreateUser

		FETCH NEXT FROM myCur INTO @nID
	End

	Close myCur
	DEALLOCATE myCur

	Select * From #tmpTable
	Drop Table #tmpTable
END
